package com.css.zfzx.fxpg.modules.buildings.data.repository;

import com.css.bpm.platform.utils.PlatformDateUtils;
import com.css.bpm.platform.utils.PlatformSessionUtils;
import com.css.bpm.platform.utils.UUIDGenerator;
import com.css.zfzx.fxpg.modules.buildings.data.repository.entity.FxBuildingsEntity;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.PropertySource;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.util.List;

/**
 * 初始化数据
 * 解决上万条数据插入慢的问题
 *
 * @Author leon
 * @Date: 2021/2/2 10:24 上午
 */
@Repository
@PropertySource("application-dev.yml")
public class FxBuildingDataUtil {
    /**
     * 数据源
     * 10.19.41.111
     */
    @Value("${spring.datasource.url}")
    private String url = "";
    @Value("${spring.datasource.username}")
    private String name = "";
    @Value("${spring.datasource.password}")
    private String pwd = "";
    private static Connection conn;
    private static PreparedStatement ps;

    /**
     * 获取数据库连接
     * SQL语句
     */
    private Connection getConnection() throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        conn = DriverManager.getConnection(url, name, pwd);
        return conn;
    }


    /**
     * sql 批量插入
     *
     * @return
     */
    public int sqlBatchSaveEntities(List<FxBuildingsEntity> list) {
        int count = 0;
        try {
            /**
             * 获取数据库连接
             */
            conn = getConnection();

            StringBuffer sql = new StringBuffer("insert into fx_buildings (id, province, city, area, building_name, structure_type, building_area, people_number, purpose, floor_number, year, lon, lat, current_intensity, geo_point, object_code, partition_flag, create_user, create_time ) "
                    + "values ");
            // 上万条数据
            for (FxBuildingsEntity entity : list) {
                count++;
                String id = UUIDGenerator.getUUID();
                String province = entity.getProvince();
                String city = entity.getCity();
                String area = entity.getArea();
                String buildingName = entity.getBuildingName();
                String structureType = entity.getStructureType();
                float buildingArea = entity.getBuildingArea();
                int peopleNumber = entity.getPeopleNumber();
                String purpose = entity.getPurpose();
                int floorNumber = entity.getFloorNumber();
                String year = entity.getYear();
                String lon = entity.getLon();
                String lat = entity.getLat();
                String currentIntensity = entity.getCurrentIntensity();
                String geo_point = "ST_GeometryFromText('POINT(" + lon + " " + lat + ")')";
                String objectCode = entity.getObjectCode();
                objectCode = (objectCode != null ? objectCode : "");
                String partitionFlag = entity.getPartitionFlag();
                partitionFlag = (partitionFlag != null ? partitionFlag : "");
                String createUser = PlatformSessionUtils.getUserId();
                createUser = (createUser != null ? createUser : "");
                Timestamp createTime = PlatformDateUtils.getCurrentTimestamp();
                /*String updateUser = PlatformSessionUtils.getUserId();
                updateUser = (updateUser != null ? updateUser : "");
                Timestamp updateTime = PlatformDateUtils.getCurrentTimestamp();*/

                sql.append("('" + id + "','" + province + "','" + city + "','" + area + "','" + buildingName +
                        "','" + structureType + "','" + buildingArea + "','" + peopleNumber + "','" + purpose + "','" + floorNumber + "','" + year
                        + "','" + lon + "','" + lat + "','" + currentIntensity + "'," + geo_point
                        + ",'" + objectCode + "','" + partitionFlag + "','" + createUser + "','" + createTime + "'),");
            }
            String sql_str = sql.toString();
            sql_str = sql_str.substring(0, sql_str.length() - 1);
            ps = conn.prepareStatement(sql_str);
            ps.executeUpdate(sql_str);
        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("第" + count + "条数据插入");
        } finally {
            try {
                if (ps != null) {
                    // 关闭连接
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return count;
    }

    public int sqlBatchDeleteEntities(List<FxBuildingsEntity> list) {
        int count = 0;
        try {
            /**
             * 获取数据库连接
             */
            conn = getConnection();

            StringBuffer sql = new StringBuffer("DELETE FROM fx_buildings WHERE uuid IN ( ");
            // 上万条数据
            for (FxBuildingsEntity entity : list) {
                count++;
                String id = entity.getId();
                sql.append("'" + id + "',");
            }
            String sql_str = sql.toString().substring(0, sql.length() - 1) + " )";
            ps = conn.prepareStatement(sql_str);
            ps.executeUpdate(sql_str);
        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("删除第" + count + "条数据出现异常!");
        } finally {
            try {
                if (ps != null) {
                    // 关闭连接
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return count;
    }

}
